CD Actual 80/CD Actual 80 Julio-Agosto 2003.iso/Linux/LinuxGazette/lg/issue72/misc/nielsen/sql/contact.table_functions

Text File | 2002-08-14 | 26.4 KB | 677 lines

drop sequence contact_sequence;
create sequence contact_sequence;
drop sequence contact_sequence_backup;
create sequence contact_sequence_backup;
drop table contact;
create table contact (
contact_id int4 NOT NULL PRIMARY KEY DEFAULT nextval('contact_sequence'),
    date_updated  timestamp NOT NULL default CURRENT_TIMESTAMP,
    date_created  timestamp NOT NULL default CURRENT_TIMESTAMP,
    active int2 CHECK (active in (0,1)) DEFAULT 0,
company_name text NOT NULL default '' ,
first text NOT NULL default '' ,
middle text NOT NULL default '' ,
last text NOT NULL default '' ,
email text NOT NULL default '' ,
work_phone text NOT NULL default '' ,
home_phone text NOT NULL default '' ,
address_1 text NOT NULL default '' ,
address_2 text NOT NULL default '' ,
address_3 text NOT NULL default '' ,
city text NOT NULL default '' ,
state text NOT NULL default '' ,
zip text NOT NULL default '' ,
country text NOT NULL default ''
);drop table contact_backup;
create table contact_backup (
backup_id int4 NOT NULL UNIQUE DEFAULT nextval('contact_sequence_backup'),
    contact_id int4 NOT NULL DEFAULT 0,
    date_updated  timestamp NOT NULL default CURRENT_TIMESTAMP,
    date_created  timestamp NOT NULL default CURRENT_TIMESTAMP,
    active int2 CHECK (active in (0,1)) DEFAULT 0,
    company_name text NOT NULL default '',
first text NOT NULL default '',
middle text NOT NULL default '',
last text NOT NULL default '',
email text NOT NULL default '',
work_phone text NOT NULL default '',
home_phone text NOT NULL default '',
address_1 text NOT NULL default '',
address_2 text NOT NULL default '',
address_3 text NOT NULL default '',
city text NOT NULL default '',
state text NOT NULL default '',
zip text NOT NULL default '',
country text NOT NULL default '', error_code text NOT NULL DEFAULT ''
);
drop view contact_active;
create view contact_active as select * from contact
        where active = 1;
drop view contact_deleted;
create view contact_deleted as select * from contact
        where active = 0;
drop view contact_backup_ids;
create view contact_backup_ids as
           select distinct contact_id from contact_backup;
drop view contact_purged;
create view contact_purged as
   select * from contact_backup where oid = ANY (
     select max(oid) from contact_backup where contact_id = ANY
        (
        select distinct contact_id from contact_backup
          where contact_backup.error_code = 'purge'
           and NOT contact_id = ANY (select contact_id from contact)
        )
        group by contact_id
     )
    ;
---              Generic Functions for Perl/Postgresql version 1.0

---                       Copyright 2001, Mark Nielsen
---                            All rights reserved.
---    This Copyright notice was copied and modified from the Perl
---    Copyright notice.
---    This program is free software; you can redistribute it and/or modify
---    it under the terms of either:

---        a) the GNU General Public License as published by the Free
---        Software Foundation; either version 1, or (at your option) any
---        later version, or

---        b) the "Artistic License" which comes with this Kit.

---    This program is distributed in the hope that it will be useful,
---    but WITHOUT ANY WARRANTY; without even the implied warranty of
---    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See either
---    the GNU General Public License or the Artistic License for more details.

---    You should have received a copy of the Artistic License with this
---    Kit, in the file named "Artistic".  If not, I'll be glad to provide one.

---    You should also have received a copy of the GNU General Public License
---   along with this program in the file named "Copying". If not, write to the
---   Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
---    02111-1307, USA or visit their web page on the internet at
---    http://www.gnu.org/copyleft/gpl.html.

-- create a method to unpurge just one item.
-- create a method to purge one item.
--  \i /tmp/Test/sample/contact.table
---------------------------------------------------------------------

drop function sql_contact_insert ();
CREATE FUNCTION sql_contact_insert () RETURNS int4 AS '
DECLARE
    record1 record;  oid1 int4; id int4 :=0; record_backup RECORD;
BEGIN
   insert into contact (date_updated, date_created, active)
        values (CURRENT_TIMESTAMP,CURRENT_TIMESTAMP, 1);
     -- Get the unique oid of the row just inserted.
   GET DIAGNOSTICS oid1 = RESULT_OID;
     -- Get the contact id.
   FOR record1 IN SELECT contact_id FROM contact where oid = oid1
      LOOP
      id := record1.contact_id;
   END LOOP;

     -- If id is NULL, insert failed or something is wrong.
   IF id is NULL THEN return (-1); END IF;
     -- It should also be greater than 0, otherwise something is wrong.
   IF id < 1 THEN return (-2); END IF;

      -- Now backup the data.
    FOR record_backup IN SELECT * FROM contact where contact_id = id
       LOOP
       insert into contact_backup (contact_id, date_updated, date_created,
           active, error_code)
         values (id, record_backup.date_updated, record_backup.date_created,
            record_backup.active, ''insert'');
    END LOOP;

     -- Everything has passed, return id as contact_id.
   return (id);
END;
' LANGUAGE 'plpgsql';
---------------------------------------------------------------------

drop function sql_contact_delete (int4);
CREATE FUNCTION sql_contact_delete (int4) RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record1 RECORD;
    record_backup RECORD;
    return_int4 int4 :=0;

BEGIN
     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

     -- If we find the id, set active = 0.
   FOR record1 IN SELECT contact_id FROM contact
          where contact_id = id
      LOOP
      update contact set active=0, date_updated = CURRENT_TIMESTAMP
           where contact_id = id;
      GET DIAGNOSTICS return_int4 = ROW_COUNT;
      id_exists := 1;
   END LOOP;

     -- If we did not find the id, abort and return -2.
   IF id_exists = 0 THEN return (-2); END IF;

   FOR record_backup IN SELECT * FROM contact where contact_id = id
      LOOP
      insert into contact_backup (contact_id, date_updated, date_created,
          active , company_name, first, middle, last, email, work_phone, home_phone, address_1, address_2, address_3, city, state, zip, country ,error_code)
           values (record_backup.contact_id, record_backup.date_updated,
             record_backup.date_updated, record_backup.active
             , record_backup.company_name, record_backup.first, record_backup.middle, record_backup.last, record_backup.email, record_backup.work_phone, record_backup.home_phone, record_backup.address_1, record_backup.address_2, record_backup.address_3, record_backup.city, record_backup.state, record_backup.zip, record_backup.country , ''delete''
      );
   END LOOP;

     -- If id_exists == 0, Return error.
     -- It means it never existed.
   IF id_exists = 0 THEN return (-1); END IF;

     -- We got this far, it must be true, return ROW_COUNT.
   return (return_int4);
END;
' LANGUAGE 'plpgsql';

---------------------------------------------------------------------

drop function sql_contact_undelete (int4);
CREATE FUNCTION sql_contact_undelete (int4) RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record1 RECORD;
    record_backup RECORD;
    return_int4 int4 :=0;

BEGIN
     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

     -- If we find the id, set active = 1.
   FOR record1 IN SELECT contact_id FROM contact
          where contact_id = id
      LOOP
      update contact set active=1, date_updated = CURRENT_TIMESTAMP
           where contact_id = id;
      GET DIAGNOSTICS return_int4 = ROW_COUNT;
      id_exists := 1;
   END LOOP;

     -- If we did not find the id, abort and return -2.
   IF id_exists = 0 THEN return (-2); END IF;

   FOR record_backup IN SELECT * FROM contact where contact_id = id
      LOOP
      insert into contact_backup (contact_id, date_updated, date_created,
          active , company_name, first, middle, last, email, work_phone, home_phone, address_1, address_2, address_3, city, state, zip, country ,error_code)
           values (record_backup.contact_id, record_backup.date_updated,
             record_backup.date_updated, record_backup.active
             , record_backup.company_name, record_backup.first, record_backup.middle, record_backup.last, record_backup.email, record_backup.work_phone, record_backup.home_phone, record_backup.address_1, record_backup.address_2, record_backup.address_3, record_backup.city, record_backup.state, record_backup.zip, record_backup.country , ''undelete''
      );
   END LOOP;

     -- If id_exists == 0, Return error.
     -- It means it never existed.
   IF id_exists = 0 THEN return (-1); END IF;

     -- We got this far, it must be true, return ROW_COUNT.
   return (return_int4);
END;
' LANGUAGE 'plpgsql';

---------------------------------------------------------------------
drop function sql_contact_update (int4 , text, text, text, text, text, text, text, text, text, text, text, text, text, text);
CREATE FUNCTION sql_contact_update  (int4 , text, text, text, text, text, text, text, text, text, text, text, text, text, text)
  RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record_update RECORD; record_backup RECORD;
    return_int4 int4 :=0;
              var_2 text;
          var_3 text;
          var_4 text;
          var_5 text;
          var_6 text;
          var_7 text;
          var_8 text;
          var_9 text;
          var_10 text;
          var_11 text;
          var_12 text;
          var_13 text;
          var_14 text;
          var_15 text;

BEGIN
             var_2 := clean_text($2);
         var_3 := clean_text($3);
         var_4 := clean_text($4);
         var_5 := clean_text($5);
         var_6 := clean_text($6);
         var_7 := clean_text($7);
         var_8 := clean_text($8);
         var_9 := clean_text($9);
         var_10 := clean_text($10);
         var_11 := clean_text($11);
         var_12 := clean_text($12);
         var_13 := clean_text($13);
         var_14 := clean_text($14);
         var_15 := clean_text($15);

     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

   FOR record_update IN SELECT contact_id FROM contact
         where contact_id = id
      LOOP
      id_exists := 1;
   END LOOP;

   IF id_exists = 0 THEN return (-2); END IF;

   update contact set date_updated = CURRENT_TIMESTAMP
      , company_name = var_2, first = var_3, middle = var_4, last = var_5, email = var_6, work_phone = var_7, home_phone = var_8, address_1 = var_9, address_2 = var_10, address_3 = var_11, city = var_12, state = var_13, zip = var_14, country = var_15
        where contact_id = id;
   GET DIAGNOSTICS return_int4 = ROW_COUNT;

   FOR record_backup IN SELECT * FROM contact where contact_id = id
      LOOP
     insert into contact_backup (contact_id,
         date_updated, date_created, active
         , company_name, first, middle, last, email, work_phone, home_phone, address_1, address_2, address_3, city, state, zip, country, error_code)
       values (record_update.contact_id, record_backup.date_updated,
         record_backup.date_updated, record_backup.active
         , record_backup.company_name, record_backup.first, record_backup.middle, record_backup.last, record_backup.email, record_backup.work_phone, record_backup.home_phone, record_backup.address_1, record_backup.address_2, record_backup.address_3, record_backup.city, record_backup.state, record_backup.zip, record_backup.country, ''update''
      );
   END LOOP;

     -- We got this far, it must be true, return ROW_COUNT.
   return (return_int4);
END;
' LANGUAGE 'plpgsql';
---------------------------------------------------------------------

drop function sql_contact_copy (int4);
CREATE FUNCTION sql_contact_copy (int4)
  RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record1 RECORD; record2 RECORD; record3 RECORD;
    return_int4 int4 := 0;
    id_new int4 := 0;
    contact_new int4 :=0;
BEGIN
     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

   FOR record1 IN SELECT contact_id FROM contact where contact_id = id
      LOOP
      id_exists := 1;
   END LOOP;
   IF id_exists = 0 THEN return (-2); END IF;

     --- Get the new id
   FOR record1 IN SELECT sql_contact_insert() as contact_insert
      LOOP
      contact_new := record1.contact_insert;
   END LOOP;
     -- If the contact_new is not greater than 0, return error.
   IF contact_new < 1 THEN return -3; END IF;

   FOR record2 IN SELECT * FROM contact where contact_id = id
      LOOP

     FOR record1 IN SELECT sql_contact_update(contact_new , clean_text(record2.company_name), clean_text(record2.first), clean_text(record2.middle), clean_text(record2.last), clean_text(record2.email), clean_text(record2.work_phone), clean_text(record2.home_phone), clean_text(record2.address_1), clean_text(record2.address_2), clean_text(record2.address_3), clean_text(record2.city), clean_text(record2.state), clean_text(record2.zip), clean_text(record2.country))
        as contact_insert
      LOOP
        -- execute some arbitrary command just to get it to pass.
      id_exists := 1;
     END LOOP;
   END LOOP;

     -- We got this far, it must be true, return new id.
   return (contact_new);
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------
drop function sql_contact_purge ();
CREATE FUNCTION sql_contact_purge () RETURNS int4 AS '
DECLARE
    record_backup RECORD; oid1 int4 := 0;
    return_int4 int4 :=0;
    deleted int4 := 0;
    delete_count int4 :=0;
    delete_id int4;

BEGIN

     -- Now delete one by one.
   FOR record_backup IN SELECT * FROM contact where active = 0
      LOOP
         -- Record the id we want to delete.
      delete_id = record_backup.contact_id;

      insert into contact_backup (contact_id, date_updated, date_created,
          active , company_name, first, middle, last, email, work_phone, home_phone, address_1, address_2, address_3, city, state, zip, country ,error_code)
           values (record_backup.contact_id, record_backup.date_updated,
             record_backup.date_updated, record_backup.active
             , record_backup.company_name, record_backup.first, record_backup.middle, record_backup.last, record_backup.email, record_backup.work_phone, record_backup.home_phone, record_backup.address_1, record_backup.address_2, record_backup.address_3, record_backup.city, record_backup.state, record_backup.zip, record_backup.country , ''purge''
          );

        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;

        -- If oid1 less than 1, return -1
      IF oid1 < 1 THEN return (-2); END IF;
        -- Now delete this from the main table.
      delete from contact where contact_id = delete_id;

        -- Get row count of row just deleted, should be 1.
      GET DIAGNOSTICS deleted = ROW_COUNT;
        -- If deleted less than 1, return -3
      IF deleted < 1 THEN return (-3); END IF;
      delete_count := delete_count + 1;

    END LOOP;

     -- We got this far, it must be true, return the number of ones we had.
   return (delete_count);
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------
drop function sql_contact_purgeone (int4);
CREATE FUNCTION sql_contact_purgeone (int4) RETURNS int4 AS '
DECLARE
    record_backup RECORD; oid1 int4 := 0;
    record1 RECORD;
    return_int4 int4 :=0;
    deleted int4 := 0;
    delete_count int4 :=0;
    delete_id int4;
    purged_no int4 := 0;

BEGIN

    delete_id := $1;
        -- If purged_id less than 1, return -4
    IF delete_id < 1 THEN return (-4); END IF;

   FOR record1 IN SELECT * FROM contact
      where active = 0 and contact_id = delete_id
      LOOP
      purged_no := purged_no + 1;
   END LOOP;

        -- If purged_no less than 1, return -1
   IF purged_no < 1 THEN return (-1); END IF;

     -- Now delete one by one.
   FOR record_backup IN SELECT * FROM contact where contact_id = delete_id
      LOOP

      insert into contact_backup (contact_id, date_updated, date_created,
          active , company_name, first, middle, last, email, work_phone, home_phone, address_1, address_2, address_3, city, state, zip, country ,error_code)
           values (record_backup.contact_id, record_backup.date_updated,
             record_backup.date_updated, record_backup.active
             , record_backup.company_name, record_backup.first, record_backup.middle, record_backup.last, record_backup.email, record_backup.work_phone, record_backup.home_phone, record_backup.address_1, record_backup.address_2, record_backup.address_3, record_backup.city, record_backup.state, record_backup.zip, record_backup.country , ''purgeone''
          );

        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;

        -- If oid1 less than 1, return -2
      IF oid1 < 1 THEN return (-2); END IF;
        -- Now delete this from the main table.
      delete from contact where contact_id = delete_id;

        -- Get row count of row just deleted, should be 1.
      GET DIAGNOSTICS deleted = ROW_COUNT;
        -- If deleted less than 1, return -3
      IF deleted < 1 THEN return (-3); END IF;
      delete_count := delete_count + 1;

    END LOOP;

     -- We got this far, it must be true, return the number of ones we had.
   return (delete_count);
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------------
drop function sql_contact_unpurge ();
CREATE FUNCTION sql_contact_unpurge () RETURNS int2 AS '
DECLARE
    record1 RECORD;
    record2 RECORD;
    record_backup RECORD;
    purged_id int4 := 0;
    purge_count int4 :=0;
    timestamp1 timestamp;
    purged_no int4 := 0;
    oid1 int4 := 0;
    oid_found int4 := 0;
    highest_oid int4 := 0;

BEGIN

     -- Now get the unique ids that were purged.
   FOR record1 IN select distinct contact_id from contact_backup
       where contact_backup.error_code = ''purge''
          and NOT contact_id = ANY (select contact_id from contact)
      LOOP

      purged_id := record1.contact_id;
      timestamp1 := CURRENT_TIMESTAMP;
      purged_no := purged_no + 1;
      oid_found := 0;
      highest_oid := 0;

        -- Now we have the unique id, find its latest date.

      FOR record2 IN select max(oid) from contact_backup
          where contact_id = purged_id and error_code = ''purge''
        LOOP
          -- record we got the date and also record the highest date.
        oid_found := 1;
        highest_oid := record2.max;
      END LOOP;

         -- If the oid_found is 0, return error.
      IF oid_found = 0 THEN return (-3); END IF;

        -- Now we have the latest date, get the values and insert them.
      FOR record_backup IN select * from contact_backup
          where oid = highest_oid
        LOOP

      insert into contact_backup (contact_id, date_updated, date_created,
          active , company_name, first, middle, last, email, work_phone, home_phone, address_1, address_2, address_3, city, state, zip, country ,error_code)
           values (purged_id, record_backup.date_updated,
             timestamp1, record_backup.active
             , record_backup.company_name, record_backup.first, record_backup.middle, record_backup.last, record_backup.email, record_backup.work_phone, record_backup.home_phone, record_backup.address_1, record_backup.address_2, record_backup.address_3, record_backup.city, record_backup.state, record_backup.zip, record_backup.country , ''unpurge''
          );

        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -1
      IF oid1 < 1 THEN return (-1); END IF;

      insert into contact (contact_id, date_updated, date_created,
          active , company_name, first, middle, last, email, work_phone, home_phone, address_1, address_2, address_3, city, state, zip, country)
           values (purged_id, timestamp1,
             timestamp1, record_backup.active
             , record_backup.company_name, record_backup.first, record_backup.middle, record_backup.last, record_backup.email, record_backup.work_phone, record_backup.home_phone, record_backup.address_1, record_backup.address_2, record_backup.address_3, record_backup.city, record_backup.state, record_backup.zip, record_backup.country );
        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -2
      IF oid1 < 1 THEN return (-2); END IF;

      END LOOP;

   END LOOP;

     -- We got this far, it must be true, return how many were affected.
   return (purged_no);
END;
' LANGUAGE 'plpgsql';

---------------------------------------------------------------------
drop function sql_contact_unpurgeone (int4);
CREATE FUNCTION sql_contact_unpurgeone (int4) RETURNS int2 AS '
DECLARE
    record_id int4;
    record1 RECORD;
    record2 RECORD;
    record_backup RECORD;
    return_int4 int4 :=0;
    purged_id int4 := 0;
    purge_count int4 :=0;
    timestamp1 timestamp;
    purged_no int4 := 0;
    oid1 int4 := 0;
    oid_found int4 := 0;
    highest_oid int4 := 0;

BEGIN

      purged_id := $1;
        -- If purged_id less than 1, return -1
      IF purged_id < 1 THEN return (-1); END IF;
        --- Get the current timestamp.
      timestamp1 := CURRENT_TIMESTAMP;

   FOR record1 IN select distinct contact_id from contact_backup
       where contact_backup.error_code = ''purge''
          and NOT contact_id = ANY (select contact_id from contact)
          and contact_id = purged_id
      LOOP
      purged_no := purged_no + 1;

   END LOOP;

        -- If purged_no less than 1, return -1
   IF purged_no < 1 THEN return (-3); END IF;

        -- Now find the highest oid.
   FOR record2 IN select max(oid) from contact_backup
          where contact_id = purged_id and error_code = ''purge''
        LOOP
          -- record we got the date and also record the highest date.
        oid_found := 1;
        highest_oid := record2.max;
    END LOOP;

         -- If the oid_found is 0, return error.
    IF oid_found = 0 THEN return (-4); END IF;

        -- Now get the data and restore it.
    FOR record_backup IN select * from contact_backup
          where oid  = highest_oid
        LOOP
        -- Insert into backup that it was unpurged.
      insert into contact_backup (contact_id, date_updated, date_created,
          active , company_name, first, middle, last, email, work_phone, home_phone, address_1, address_2, address_3, city, state, zip, country ,error_code)
           values (purged_id, timestamp1,
             record_backup.date_created, record_backup.active
             , record_backup.company_name, record_backup.first, record_backup.middle, record_backup.last, record_backup.email, record_backup.work_phone, record_backup.home_phone, record_backup.address_1, record_backup.address_2, record_backup.address_3, record_backup.city, record_backup.state, record_backup.zip, record_backup.country , ''unpurgeone''
          );

        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -1
      IF oid1 < 1 THEN return (-1); END IF;
        -- Insert into live table.
      insert into contact (contact_id, date_updated, date_created,
          active , company_name, first, middle, last, email, work_phone, home_phone, address_1, address_2, address_3, city, state, zip, country)
           values (record_backup.contact_id, timestamp1,
             record_backup.date_updated, record_backup.active
             , record_backup.company_name, record_backup.first, record_backup.middle, record_backup.last, record_backup.email, record_backup.work_phone, record_backup.home_phone, record_backup.address_1, record_backup.address_2, record_backup.address_3, record_backup.city, record_backup.state, record_backup.zip, record_backup.country );
        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -2
      IF oid1 < 1 THEN return (-2); END IF;

      END LOOP;

     -- We got this far, it must be true, return how many were affected (1).
   return (purged_no);
END;
' LANGUAGE 'plpgsql';

insert into contact (contact_id, date_updated, date_created, active)
    values (0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0);
insert into contact_backup (backup_id, contact_id,
     date_updated, date_created, active, error_code)
    values (0, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0, 'table creation');




drop function clean_text (text);
CREATE FUNCTION  clean_text (text) RETURNS text AS '
  my $Text = shift;
    # Get rid of whitespace in front.
  $Text =~ s/^\\s+//;
    # Get rid of whitespace at end.
  $Text =~ s/\\s+$//;
    # Get rid of anything not text.
  $Text =~ s/[^ a-z0-9\\/\\`\\~\\!\\@\\#\\$\\%\\^\\&\\*\\(\\)\\-\\_\\=\\+\\\\\\|\[\\{\\]\\}\\;\\:\\''\\"\\,\\<\\.\\>\\?\\t\\n]//gi;
    # Replace all multiple whitespace with one space.
  $Text =~ s/\\s+/ /g;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up.
select clean_text ('       ,./<>?aaa aa      !@#$%^&*()_+| ');

drop function clean_alpha (text);
CREATE FUNCTION  clean_alpha (text) RETURNS text AS '
  my $Text = shift;
  $Text =~ s/[^a-z0-9_]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up.
select clean_alpha ('       ,./<>?aaa aa      !@#$%^&*()_+| ');

drop function clean_numeric (text);
CREATE FUNCTION  clean_numeric (text) RETURNS int4 AS '
  my $Text = shift;
  $Text =~ s/[^0-9]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up.
select clean_numeric ('       ,./<>?aaa aa      !@#$%^&*()_+| ');

drop function clean_numeric (int4);
CREATE FUNCTION  clean_numeric (int4) RETURNS int4 AS '
  my $Text = shift;
  $Text =~ s/[^0-9]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up.
select clean_numeric (11111);



select sql_contact_insert();
select sql_contact_update(1,1,'Company ABC','Dummy1','','Account','[e-mail]','','','','','','San Jose','CA','95135','USA');

select sql_contact_insert();
select sql_contact_update(2,1,'Company ABC','Dummy2','','Account','[e-mail]','','','','','','Columbus','OH','43221','USA');

vacuum;